package com.studentManagement.daoImpl;

import com.studentManagement.dao.StudentDao;
import com.studentManagement.po.Student;
import com.studentManagement.po.Student_Leader;
import com.studentManagement.po.Student_simple;
import com.studentManagement.utils.JdbcUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by Administrator on 2017/5/1 0001.
 */
public class StudentDaoImpl implements StudentDao {

    private Connection conn = JdbcUtil.getConnection();
    private ResultSet resultSet = null;
    private PreparedStatement preparedStatement = null;

    @Override//查询学生信息（class_id）（sql分页）
    public List<Student_simple> queryStudentByClass_id(String class_id, int begin, int end) {
        List<Student_simple> arrayList = new ArrayList<>();
        String sql = "SELECT\n" +
                "\tstudent_id,\n" +
                "\tstudent_name,\n" +
                "\tstudent_sex,\n" +
                "\tstudent_state,\n" +
                "\tstudent_qq\n" +
                "FROM\n" +
                "\tstudents\n" +
                "WHERE student_class_id=?\n" +
                "LIMIT ?,?";

        try {
            preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setString(1, class_id);
            preparedStatement.setInt(2, begin);
            preparedStatement.setInt(3, end);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                Student_simple student = new Student_simple();
                student.setStudent_id(resultSet.getString(1));
                student.setStudent_name(resultSet.getString(2));
                student.setStudent_sex(resultSet.getString(3));
                student.setStudent_state(resultSet.getInt(4));
                student.setStudent_qq(resultSet.getString(5));
                arrayList.add(student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.closeStatementAndResultSet(resultSet, preparedStatement);
            JdbcUtil.closeConnection(conn);
        }
        return arrayList;
    }

    @Override//查询学生头像路径（student_id)
    public String queryStudentImg(String student_id) {
        String sql="SELECT students.student_image_Path FROM students WHERE student_id = ?";
        String stuentImg="";
        try {
            preparedStatement=conn.prepareStatement(sql);
            preparedStatement.setString(1,student_id);
            resultSet=preparedStatement.executeQuery();
            while (resultSet.next()){
                stuentImg=resultSet.getString(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtil.closeStatementAndResultSet(resultSet,preparedStatement);
            JdbcUtil.closeConnection(conn);
        }
        return stuentImg;
    }

    @Override//查询学生详细信息（student_id)
    public Student queryStudentDetail(String student_id) {
        String sql="SELECT *FROM students WHERE student_id=?";
        Student student =new Student();
        try {
            preparedStatement=conn.prepareStatement(sql);
            preparedStatement.setString(1,student_id);
            resultSet=preparedStatement.executeQuery();
            while (resultSet.next()){
                student.setStudent_id(resultSet.getString(1));
                student.setStudent_name(resultSet.getString(2));
                student.setStudent_key(resultSet.getString(3));
                student.setStudent_sex(resultSet.getString(4));
                student.setStudent_age(resultSet.getInt(5));
                student.setStudent_state(resultSet.getInt(6));
                student.setStudent_credit(resultSet.getDouble(7));
                student.setStudent_phone(resultSet.getString(8));
                student.setStudent_address(resultSet.getString(9));
                student.setStudent_qq(resultSet.getString(10));
                student.setStudent_nation(resultSet.getString(11));
                student.setStudent_dormitory_id(resultSet.getString(12));
                student.setStudent_class_id(resultSet.getString(13));
                student.setStudent_duty(resultSet.getString(14));
                student.setStudent_enter_year(resultSet.getString(15));
                student.setStudent_image_Path(resultSet.getString(16));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtil.closeStatementAndResultSet(resultSet,preparedStatement);
            JdbcUtil.closeConnection(conn);
        }

        return student;
    }

    @Override//更新学生详细信息（student_id)
    public boolean updateStudentDetail(Student student) {
        boolean boo=false;
        String sql="UPDATE students\n" +
            "SET student_name =?, student_key =?, student_sex =?, student_age =?, student_state =?, student_credit =?, student_phone =?, student_address =?, student_qq =?, student_nation =?, student_dormitory_id =?, student_class_id =?, student_duty =?, student_enter_year =?,\n" +
            " student_image_Path =?\n" +
            "WHERE\n" +
            "\tstudent_id =?";
        try {
            preparedStatement=conn.prepareStatement(sql);
            preparedStatement.setString(1,student.getStudent_name());
            preparedStatement.setString(2,student.getStudent_key());
            preparedStatement.setString(3,student.getStudent_sex());
            preparedStatement.setInt(4,student.getStudent_age());
            preparedStatement.setInt(5,student.getStudent_state());
            preparedStatement.setDouble(6,student.getStudent_credit());
            preparedStatement.setString(7,student.getStudent_phone());
            preparedStatement.setString(8,student.getStudent_address());
            preparedStatement.setString(9,student.getStudent_qq());
            preparedStatement.setString(10,student.getStudent_nation());
            preparedStatement.setString(11,student.getStudent_dormitory_id());
            preparedStatement.setString(12,student.getStudent_class_id());
            preparedStatement.setString(13,student.getStudent_duty());
            preparedStatement.setString(14,student.getStudent_enter_year());
            preparedStatement.setString(15,student.getStudent_image_Path());
            preparedStatement.setString(16,student.getStudent_id());
            int i =preparedStatement.executeUpdate();
            if(i==1)boo=true;
            else boo=false;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtil.closeStatementAndResultSet(resultSet,preparedStatement);
            JdbcUtil.closeConnection(conn);
        }
        return boo;
    }

    @Override//查询学生人数（class_id）
    public int queryStudentSumByClass_id(String class_id) throws SQLException {
        String sql = "SELECT count(*) FROM students WHERE student_class_id=?";
        int sum = 0;
        try {
            preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setString(1, class_id);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next())
                sum = resultSet.getInt(1);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.closeStatementAndResultSet(resultSet, preparedStatement);
            JdbcUtil.closeConnection(conn);
        }
        return sum;
    }

    @Override  //根据关键词查询学生后的总条目
    public int queryStudentSumByKeywords(String keywords) throws SQLException {
        String sql="SELECT DISTINCT count(*)\n" +
            "FROM\n" +
            "\tstudents\n" +
            "WHERE\n" +
            "\tstudent_id LIKE ? || student_name LIKE ? || student_sex LIKE ? || student_phone LIKE ? || student_address LIKE ? || student_qq LIKE ? || student_dormitory_id LIKE ? || student_duty LIKE ? " ;
        keywords="%"+keywords+"%";
        int sum=0;
        try {
            preparedStatement=conn.prepareStatement(sql);
            preparedStatement.setString(1,keywords);
            preparedStatement.setString(2,keywords);
            preparedStatement.setString(3,keywords);
            preparedStatement.setString(4,keywords);
            preparedStatement.setString(5,keywords);
            preparedStatement.setString(6,keywords);
            preparedStatement.setString(7,keywords);
            preparedStatement.setString(8,keywords);
            resultSet=preparedStatement.executeQuery();
            while (resultSet.next()){
                sum=resultSet.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtil.closeStatementAndResultSet(resultSet,preparedStatement);
            JdbcUtil.closeConnection(conn);
        }

        return sum;
    }

    @Override//添加学生(Student)
    public void addStudent(Student student) throws SQLException {
        String sql = "INSERT INTO students (\n" +
                "\tstudent_id,\n" +
                "\tstudent_name,\n" +
                "\tstudent_key,\n" +
                "\tstudent_sex,\n" +
                "\tstudent_age,\n" +
                "\tstudent_state,\n" +
                "\tstudent_credit,\n" +
                "\tstudent_phone,\n" +
                "\tstudent_address,\n" +
                "\tstudent_qq,\n" +
                "\tstudent_nation,\n" +
                "\tstudent_dormitory_id,\n" +
                "\tstudent_class_id,\n" +
                "\tstudent_duty,\n" +
                "\tstudent_enter_year,\n" +
                "\tstudent_image_Path\n" +
                ")\n" +
                "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        try {
            preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setString(1, student.getStudent_id());
            preparedStatement.setString(2, student.getStudent_name());
            preparedStatement.setString(3, student.getStudent_key());
            preparedStatement.setString(4, student.getStudent_sex());
            preparedStatement.setInt(5, student.getStudent_age());
            preparedStatement.setInt(6, student.getStudent_state());
            preparedStatement.setDouble(7, student.getStudent_credit());
            preparedStatement.setString(8, student.getStudent_phone());
            preparedStatement.setString(9, student.getStudent_address());
            preparedStatement.setString(10, student.getStudent_qq());
            preparedStatement.setString(11, student.getStudent_nation());
            preparedStatement.setString(12, student.getStudent_dormitory_id());
            preparedStatement.setString(13, student.getStudent_class_id());
            preparedStatement.setString(14, student.getStudent_duty());
            preparedStatement.setString(15, student.getStudent_enter_year());
            preparedStatement.setString(16, student.getStudent_image_Path());
            preparedStatement.executeUpdate();
        } catch (Exception e) {
            throw new SQLException();
        } finally {
            JdbcUtil.closeStatementAndResultSet(resultSet, preparedStatement);
            JdbcUtil.closeConnection(conn);
        }
    }

    @Override //删除学生（student_id)
    public boolean deleteStudent(String student_id) {
        String sql="DELETE FROM students WHERE student_id = ?";
        int result=0;
        try {
            preparedStatement=conn.prepareStatement(sql);
            preparedStatement.setString(1,student_id);
            result=preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtil.closeStatementAndResultSet(resultSet,preparedStatement);
            JdbcUtil.closeConnection(conn);
        }
        if (result==1) return true;
        else   return false;
    }

    @Override//查询学号是否可用（student_id）
    public boolean Student_idCanUsed(String student_id) {
        String sql = "select student_id from students WHERE student_id=?";
        boolean boo = false;
        try {
            preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setString(1, student_id);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                boo = false;
            } else {
                boo = true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.closeStatementAndResultSet(resultSet,preparedStatement);
            JdbcUtil.closeConnection(conn);
            return boo;
        }
    }

    //查询班委（不包括室长）
    @Override
    public List<Student_Leader> queryStudentLeader(String class_id) {
        String sql = "SELECT\n" +
                "\tstudents.student_duty,\n" +
                "\tstudents.student_id,\n" +
                "\tstudents.student_name,\n" +
                "\tstudents.student_sex,\n" +
                "\tstudents.student_qq\n" +
                "FROM\n" +
                "\tstudents\n" +
                "WHERE\n" +
                "\tstudents.student_class_id = ?\n" +
                "AND student_duty IN (\n" +
                "\t'班长',\n" +
                "\t'副班长',\n" +
                "\t'团支书',\n" +
                "\t'团支书',\n" +
                "\t'学习委员',\n" +
                "\t'纪律委员',\n" +
                "\t'生活委员',\n" +
                "\t'组宣委员',\n" +
                "\t'心理委员',\n" +
                "\t'体育委员'\n" +
                ")\n" +
                "ORDER BY\n" +
                "\tFIELD(\n" +
                "\t\t`student_duty`,\n" +
                "\t\t'班长',\n" +
                "\t\t'副班长',\n" +
                "\t\t'团支书',\n" +
                "\t\t'团支书',\n" +
                "\t\t'学习委员',\n" +
                "\t\t'纪律委员',\n" +
                "\t\t'生活委员',\n" +
                "\t\t'组宣委员',\n" +
                "\t\t'心理委员',\n" +
                "\t\t'体育委员'\n" +
                "\t)";
        List<Student_Leader> list = new ArrayList<>();
        try {
            preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setString(1, class_id);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                Student_Leader student_leader = new Student_Leader();
                student_leader.setStudent_duty(resultSet.getString(1));
                student_leader.setStudent_id(resultSet.getString(2));
                student_leader.setStudent_name(resultSet.getString(3));
                student_leader.setStudent_sex(resultSet.getString(4));
                student_leader.setStudent_qq(resultSet.getString(5));
                list.add(student_leader);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.closeStatementAndResultSet(resultSet, preparedStatement);
            JdbcUtil.closeConnection(conn);
        }
        return list;
    }

    @Override//查询班级委员的详细信息（class_id）
    public List<Student> queryStudentLeaderDetail(String class_id) {
        String sql = "SELECT\n" +
            "\tstudents.student_duty,\n" +
            "\tstudents.student_id,\n" +
            "\tstudents.student_name,\n" +
            "\tstudents.student_sex,\n" +
            "\tstudents.student_age,\n" +
            "\tstudents.student_state,\n" +
            "\tstudents.student_credit,\n" +
            "\tstudents.student_phone,\n" +
            "\tstudents.student_address,\n" +
            "\tstudents.student_qq,\n" +
            "\tstudents.student_nation,\n" +
            "\tstudents.student_dormitory_id,\n" +
            "\tstudents.student_class_id,\n" +
            "\tstudents.student_duty,\n" +
            "\tstudents.student_enter_year\n" +
            "FROM\n" +
            "\tstudents\n" +
            "WHERE\n" +
            "\tstudents.student_class_id = ?\n" +
            "AND student_duty IN (\n" +
            "\t'班长',\n" +
            "\t'副班长',\n" +
            "\t'团支书',\n" +
            "\t'团支书',\n" +
            "\t'学习委员',\n" +
            "\t'纪律委员',\n" +
            "\t'生活委员',\n" +
            "\t'组宣委员',\n" +
            "\t'心理委员',\n" +
            "\t'体育委员'\n" +
            ")\n" +
            "ORDER BY\n" +
            "\tFIELD(\n" +
            "\t\t`student_duty`,\n" +
            "\t\t'班长',\n" +
            "\t\t'副班长',\n" +
            "\t\t'团支书',\n" +
            "\t\t'团支书',\n" +
            "\t\t'学习委员',\n" +
            "\t\t'纪律委员',\n" +
            "\t\t'生活委员',\n" +
            "\t\t'组宣委员',\n" +
            "\t\t'心理委员',\n" +
            "\t\t'体育委员'\n" +
            "\t)";
        List<Student> list = new ArrayList<>();
        try {
            preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setString(1, class_id);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                Student student = new Student();
                student.setStudent_duty(resultSet.getString(1));
                student.setStudent_id(resultSet.getString(2));
                student.setStudent_name(resultSet.getString(3));
                student.setStudent_sex(resultSet.getString(4));
                student.setStudent_age(resultSet.getInt(5));
                student.setStudent_state(resultSet.getInt(6));
                student.setStudent_credit(resultSet.getDouble(7));
                student.setStudent_phone(resultSet.getString(8));
                student.setStudent_address(resultSet.getString(9));
                student.setStudent_qq(resultSet.getString(10));
                student.setStudent_nation(resultSet.getString(11));
                student.setStudent_dormitory_id(resultSet.getString(12));
                student.setStudent_class_id(resultSet.getString(13));
                student.setStudent_enter_year(resultSet.getString(15));
                list.add(student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.closeStatementAndResultSet(resultSet, preparedStatement);
            JdbcUtil.closeConnection(conn);
        }
        return list;
    }

    @Override//根据关键词来获取相关的学生信息(第pageNum页，每页onePage条)
    public List<Student> getStudentDetail_byKeywords(String keyWords,int pageNum,int onePage) {
        List<Student> list=new ArrayList<>();
        String sql="SELECT DISTINCT\n" +
            "\t*\n" +
            "FROM\n" +
            "\tstudents\n" +
            "WHERE\n" +
            "\tstudent_id LIKE ? || student_name LIKE ? || student_sex LIKE ? || student_phone LIKE ? || student_address LIKE ? || student_qq LIKE ? || student_dormitory_id LIKE ? || student_duty LIKE ? " +
            "LIMIT ?,?";
        keyWords="%"+keyWords+"%";
        try {
            preparedStatement=conn.prepareStatement(sql);
            preparedStatement.setString(1,keyWords);
            preparedStatement.setString(2,keyWords);
            preparedStatement.setString(3,keyWords);
            preparedStatement.setString(4,keyWords);
            preparedStatement.setString(5,keyWords);
            preparedStatement.setString(6,keyWords);
            preparedStatement.setString(7,keyWords);
            preparedStatement.setString(8,keyWords);
            preparedStatement.setInt(9,(pageNum-1)*onePage);
            preparedStatement.setInt(10,onePage);
            resultSet=preparedStatement.executeQuery();

            while (resultSet.next()){
                Student student=new Student();
                student.setStudent_id(resultSet.getString(1));
                student.setStudent_name(resultSet.getString(2));
                student.setStudent_key(resultSet.getString(3));
                student.setStudent_sex(resultSet.getString(4));
                student.setStudent_age(resultSet.getInt(5));
                student.setStudent_state(resultSet.getInt(6));
                student.setStudent_credit(resultSet.getDouble(7));
                student.setStudent_phone(resultSet.getString(8));
                student.setStudent_address(resultSet.getString(9));
                student.setStudent_qq(resultSet.getString(10));
                student.setStudent_nation(resultSet.getString(11));
                student.setStudent_dormitory_id(resultSet.getString(12));
                student.setStudent_class_id(resultSet.getString(13));
                student.setStudent_duty(resultSet.getString(14));
                student.setStudent_enter_year(resultSet.getString(15));
                student.setStudent_image_Path(resultSet.getString(16));
                list.add(student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtil.closeStatementAndResultSet(resultSet,preparedStatement);
            JdbcUtil.closeConnection(conn);
        }
        return list;
    }
}
